The invention generally concerns the use of computer spreadsheets, and in particular, relates to binding spreadsheet cells to external objects.
During the past decade, computer software has evolved from independent applications running alone, to integrated suites of applications capable of sharing information between applications in the suite. The applications may be running either on the same computer or on different computers connected via a network. In order to share information between different software applications, a communication scheme for transferring the data is required. One such scheme is the Dynamic Data Exchange (DDE) protocol that is used to transfer data between application programs running on Microsoft Corporation""s WINDOWS(trademark) operating systems.
DDE is a standard inter-application communication protocol built into Microsoft Corporation""s WINDOWS(trademark) operating systems, such as WINDOWS(trademark) 3.x, WINDOWS 95(trademark), WINDOWS 98(trademark), and WINDOWS NT(trademark), (hereinafter collectively or singularly referred to as WINDOWS(trademark) operating systems. This protocol allows WINDOWS(trademark) programs that support DDE to exchange data between themselves. DDE can be thought of as a direct conversation between two application programs. In most cases, one application is providing some form of data (either text or graphics) to another application. The application that is the source of the data is called the xe2x80x9cserverxe2x80x9d and the application that is receiving the data is called the xe2x80x9cclient.xe2x80x9d
Each data item that a server application can provide has a unique identifier consisting of three parts: a DDE Application Name; a DDE Topic; and a DDE Item Name. The DDE Application Name is almost always the executable filename for the server application (without the xe2x80x9c.exexe2x80x9d extension). The DDE Topic typically identifies a group or category of data in the server application, and each data item that a server can provide has a unique DDE Item Name. Thus, the Application Name, Topic, and Item Name uniquely identify the source of the data in a server application that is to be linked to a client application.
DDE links are always initiated in the client application. The client initiates a DDE link by broadcasting a message containing a DDE Application Name, a DDE Topic, and optionally a DDE Item to all other applications currently running. If a server application is running that can provide the data, it responds to the xe2x80x9cDDE initiatexe2x80x9d command and the operating system opens a xe2x80x9clinkxe2x80x9d between the two applications. Fortunately, most WINDOWS(trademark) programs that support DDE insulate the user from the low-level details of establishing DDE links and simply allow the user to specify the Application Name, Topic, and Item Name for a specific piece of data and the link is then automatically established by the user""s application program.
More recent versions of the WINDOWS(trademark) operating systems support a feature called xe2x80x9cNetwork DDE.xe2x80x9d Network DDE allows DDE data to be passed across a network from one computer to another, allowing the client and server applications to run on different computers connected across a network. Network DDE must be enabled in Windows by running the program xe2x80x9cNETDDE.EXExe2x80x9d before any data can be transferred.
It is often desirable to link or xe2x80x9cbindxe2x80x9d information in a spreadsheet cell to information contained in an external data source. For example, a spreadsheet that calculates gross revenues for a business may contain cells that are linked to data contained in another application program, such as a database application. For example, DDE can be used to dynamically link a cell in a Microsoft EXCEL(trademark) spreadsheet to a data field in the database application program running on another computer. A user would enter the following formula in the spreadsheet cell and then press the Enter key:
=  ComputerName NDDE$|$MyPage.dde!Fieldxe2x80x83xe2x80x83(1).
The formula contains the three parts necessary to successfully link the client application to server the application program, including the Network DDE Application Name, which is referenced by the name of the computer running the DDE Server program, followed by a single slash and the word xe2x80x9cNDDE$.xe2x80x9d The DDE Topic appears after the dollar sign, referenced by the xe2x80x9cPage Namexe2x80x9d that is assigned in the ClipBook application, followed by a period and the word xe2x80x9cdde.xe2x80x9d Finally, the specific DDE Item Name (xe2x80x98Field(1)xe2x80x99) is included.
Upon activation of the Enter key, EXCEL(trademark) will automatically establish a DDE link between the field in the server application program and the spreadsheet cell. (The server application program must be running on a computer coupled to the computer running the client application over a network in order for the above link formula to be successful). After the link is established, any data in the text box xe2x80x9cField(1)xe2x80x9d in the server application program will automatically appear in the xe2x80x9clinkedxe2x80x9d cell in the spreadsheet. Also, whenever the data for Field(1) in the server application program changes, the contents of the spreadsheet cell will automatically be updated with the new data. Effectively, the operating system does an automatic cut and paste from the server application to the client application whenever the server application""s data changes.
Either application involved in a DDE data link can terminate the link. Some applications have menu options that allow a user to selectively terminate any open DDE links. Closing either of the linked applications also causes all active links between the two programs to be terminated.
The process of setting up a network DDE connection is fairly simple and involves using the xe2x80x9cClipBookxe2x80x9d application found in the xe2x80x9cMainxe2x80x9d program group in the WINDOWS(trademark) operating systems. The ClipBook extends the concept of the WINDOWS(trademark) Clipboard across a network. The ClipBook allows a user to create xe2x80x9cClipBook Pagesxe2x80x9d that can be xe2x80x9csharedxe2x80x9d with other computers on a network. Once a user creates and shares a page on one computer, other computers on the network can xe2x80x9cconnectxe2x80x9d to that page. If the page contains data from a DDE server, then the user can set up DDE links to the data, and the data will be passed to any client application on the network just as if the client application were running on the same local computer as the server application.
After a user creates a xe2x80x9cPage Namexe2x80x9d using the ClipBook application and pastes data from a DDE server into the page, the page name and the DDE connection data contained in it will continue to exist even after the ClipBook application is closed on both computers. The WINDOWS(trademark) operating systems can even shut down on both computers without terminating the link, since upon restarting, the client application will be able to re-establish the DDE link using the same Network DDE Application, Topic, and Item Name.
While DDE and Network DDE are beneficial, they have their limitations. These protocols communicate by passing messages between xe2x80x9cwindowsxe2x80x9d (each application in the WINDOWS(trademark) operating systems runs in it own separate xe2x80x9cwindowxe2x80x9d). Each application must process the messages it receives through its event loop message processing sequence, which leads to extra overhead and slows down throughput. The server application must also create and send a message to all external clients every time a linked field changes its value. In addition, only WINDOWS(trademark) programs support DDE and Network DDE. Thus, a user cannot establish a link to data contained in a non-WINDOWS(trademark) application program using DDE or Network DDE.
Clearly, it would be desirable to provide a scheme that enables a user to bind spreadsheet cells to various external data sources (objects), while providing faster throughput and less overhead than when using DDE. It would also be advantageous to provide a scheme that enables data from bound objects to be retrieved from computers that use operating systems other than the WINDOWS(trademark) operating systems, and from applications that are written in different languages. In addition, it would be desirable to be able to link spreadsheet cells to the methods exposed by such external objects.
The present invention addresses the shortcomings of DDE and other prior art by providing a method for binding spreadsheet cells to external objects that incorporates a direct communication scheme with little overhead. The method allows a spreadsheet cell to be bound to an object external to the spreadsheet that exposes a method or property of the object. The exposed property or method can then be used to update the value of the spreadsheet cell, either automatically when the exposed property changes, or upon request from the spreadsheet. The method preferably is implemented through the use of Microsoft Corporation""s Component Object Model (COM) architecture. Alternately, the method can be implemented using other object model architectures that are functionally similar to COM, including Sun Corporation""s JAVA BEANS(trademark).
According to a first aspect of the invention, a method is provided for binding a spreadsheet cell to a property exposed by an external object. A formula for the cell is created comprising a portion that references a property of an external object. The formula is then parsed to extract the portion of the formula that references the external object and its property. A property change notice is set up between the spreadsheet and the object based on the extracted reference information and is then forwarded to the spreadsheet whenever the object""s property changes. When such a change occurs, a value of the changed property is extracted from the object. The formula in the cell is then recalculated based on the changed property value that it references, and other cells that contain a formula that includes a reference to the cell are recalculated. In one preferred embodiment, the spreadsheet is a Microsoft OFFICE 2000(trademark) spreadsheet COM control. Additionally, the spreadsheet COM control and the external object preferably are both contained in a web-page document that is being browsed by a Microsoft Corporation INTERNET EXPLORER(trademark) browser.
According to a second aspect of the invention, a method is provided for binding a spreadsheet cell to a method (function) or property exposed by an external object. A formula that comprises a reference to the object is created, wherein the formula includes an identity of the object and a method or property exposed by the object. The formula is then extracted to identify the object and its exposed method or property. The cell is bound to the object""s exposed method or cell by setting up a link to the object so that the object""s exposed method is executed or its exposed property is retrieved when a recalculation is performed on the cell by the spreadsheet program. The object is preferably part of a top-level document, and the reference in the formula preferably includes a portion that identifies the top-level document. The top-level document preferably provides an interface that returns an identifier to the object and its exposed method or property, wherein the object""s method or property is accessed by using the identifier.
Another aspect of the present invention is directed to a system that includes a memory in which a plurality of machine instructions are stored, and a processor that is coupled to the memory. The processor executes the machine instructions and implements a plurality of functions that are generally consistent with the steps of the method described above.